﻿Imports System.Data.SqlClient
Imports UTNComputers.clsVentas
Imports UTNComputers.clsVentas_Detalle
Imports UTNComputers.daoVenta_Detalle
Imports UTNComputers.clsCliente
Imports UTNComputers.daoCliente
Imports UTNComputers.clsEmpleado
Imports UTNComputers.daoEmpleado
Imports UTNComputers.clsPresupuesto
Imports UTNComputers.daoPresupuesto

Public Class daoVenta
    'Funcion para el alta  de una Venta'
    Public Function Alta(ByVal Venta As clsVentas) As Boolean
        Dim Cn As New SqlConnection
        Dim Cm As New SqlCommand

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_ALTA_VENTA"

            Cm.Parameters.Add("@vta_id", SqlDbType.Int)
            Cm.Parameters.Add("@vta_fecha", SqlDbType.SmallDateTime)
            Cm.Parameters.Add("@cli_id", SqlDbType.Int)
            Cm.Parameters.Add("@presu_id", SqlDbType.Int)
            Cm.Parameters.Add("@emp_id", SqlDbType.Int)
            Cm.Parameters.Add("@vta_importe", SqlDbType.Decimal)
            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)

            Cm.Parameters("@vta_id").Value = Venta.Vta_Id
            Cm.Parameters("@vta_fecha").Value = Venta.Vta_Fecha
            Cm.Parameters("@cli_id").Value = Venta.Cli_Id
            Cm.Parameters("@presu_id").Value = Venta.Presu_Id
            Cm.Parameters("@emp_id").Value = Venta.Emp_Id
            Cm.Parameters("@vta_importe").Value = Venta.Vta_Importe
            Cm.Parameters("@Cod_Suc").Value = Venta.Cod_Suc

            Cm.Connection = Cn

            Cm.ExecuteNonQuery()
            Cn.Close()
            Return True

        Catch ex As Exception
            Return False
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para la modificacion de una Venta'
    Public Function Modificar(ByVal Venta As clsVentas) As Boolean
        Dim Cn As New SqlConnection
        Dim Cm As New SqlCommand

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_MODI_VENTA"

            Cm.Parameters.Add("@vta_id", SqlDbType.Int)
            Cm.Parameters.Add("@vta_fecha", SqlDbType.SmallDateTime)
            Cm.Parameters.Add("@cli_id", SqlDbType.Int)
            Cm.Parameters.Add("@presu_id", SqlDbType.Int)
            Cm.Parameters.Add("@emp_id", SqlDbType.Int)
            Cm.Parameters.Add("@vta_importe", SqlDbType.Decimal)
            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)

            Cm.Parameters("@vta_id").Value = Venta.Vta_Id
            Cm.Parameters("@vta_fecha").Value = Venta.Vta_Fecha
            Cm.Parameters("@cli_id").Value = Venta.Cli_Id
            Cm.Parameters("@presu_id").Value = Venta.Presu_Id
            Cm.Parameters("@emp_id").Value = Venta.Emp_Id
            Cm.Parameters("@vta_importe").Value = Venta.Vta_Importe
            Cm.Parameters("@Cod_Suc").Value = Venta.Cod_Suc

            Cm.Connection = Cn

            Cm.ExecuteNonQuery()
            Cn.Close()
            Return True

        Catch ex As Exception
            Return False
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer una Venta'
    Public Function TraeVenta(ByVal vta_id As Integer) As clsVentas
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim Venta As New clsVentas()

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_VENTA"

            Cm.Parameters.Add("@vta_id", SqlDbType.Int)
            Cm.Parameters("@vta_id").Value = vta_id

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Venta.Vta_Id = Dr.GetInt32(0)
                    Venta.Vta_Fecha = Dr.GetDateTime(1)
                    If Dr.IsDBNull(2) Then
                        Venta.Cli_Id = 0
                    Else
                        Venta.Cli_Id = Dr.GetInt32(2)
                    End If

                    Venta.Presu_Id = Dr.GetInt32(3)
                    If Dr.IsDBNull(3) Then
                        Venta.Presu_Id = 0
                    Else
                        Venta.Presu_Id = Dr.GetInt32(3)
                    End If

                    Venta.Emp_Id = Dr.GetInt32(4)
                    Venta.Vta_Importe = Dr.GetDecimal(5)
                    Venta.Cod_Suc = Dr.GetInt32(6)

                    If (Venta.Emp_Id > 0) Then
                        Dim Empleado_Dao As New daoEmpleado()
                        Venta.Empleado = Empleado_Dao.TraeEmpleado(Venta.Emp_Id)
                        Empleado_Dao.Dispose()
                    Else
                        Venta.Empleado = New clsEmpleado()
                    End If

                    If (Venta.Cli_Id > 0) Then
                        Dim Cliente_Dao As New daoCliente()
                        Venta.Cliente = Cliente_Dao.TraeCliente(Venta.Cli_Id)
                        Cliente_Dao.Dispose()
                    Else
                        Venta.Cliente = New clsCliente()
                    End If


                    Dim ListaDetalle_Venta As New List(Of clsVentas_Detalle)
                    Dim Detalle_Venta_Dao As New daoVenta_Detalle()
                    ListaDetalle_Venta = Detalle_Venta_Dao.TraeDetallesVenta(Venta.Vta_Id)
                    Venta.Detalle = ListaDetalle_Venta
                    Detalle_Venta_Dao.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return Venta
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer Todas las Ventas por Rango de Fecha'
    Public Function TraeVentas(ByVal fec_Desde As Date, ByVal fec_Hasta As Date) As List(Of clsVentas)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaVentas As New List(Of clsVentas)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_VENTAS_X_FECHA"

            Cm.Parameters.Add("@fecha_desde", SqlDbType.SmallDateTime)
            Cm.Parameters.Add("@fecha_hasta", SqlDbType.SmallDateTime)
            Cm.Parameters("@fecha_desde").Value = fec_Desde
            Cm.Parameters("@fecha_hasta").Value = fec_Hasta

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Venta As New clsVentas()
                    Venta.Vta_Id = Dr.GetInt32(0)
                    Venta.Vta_Fecha = Dr.GetDateTime(1)
                    If Dr.IsDBNull(2) Then
                        Venta.Cli_Id = 0
                    Else
                        Venta.Cli_Id = Dr.GetInt32(2)
                    End If

                    Venta.Presu_Id = Dr.GetInt32(3)
                    If Dr.IsDBNull(3) Then
                        Venta.Presu_Id = 0
                    Else
                        Venta.Presu_Id = Dr.GetInt32(3)
                    End If

                    Venta.Emp_Id = Dr.GetInt32(4)
                    Venta.Vta_Importe = Dr.GetDecimal(5)
                    Venta.Cod_Suc = Dr.GetInt32(6)

                    If (Venta.Emp_Id > 0) Then
                        Dim Empleado_Dao As New daoEmpleado()
                        Venta.Empleado = Empleado_Dao.TraeEmpleado(Venta.Emp_Id)
                        Empleado_Dao.Dispose()
                    Else
                        Venta.Empleado = New clsEmpleado()
                    End If

                    If (Venta.Cli_Id > 0) Then
                        Dim Cliente_Dao As New daoCliente()
                        Venta.Cliente = Cliente_Dao.TraeCliente(Venta.Cli_Id)
                        Cliente_Dao.Dispose()
                    Else
                        Venta.Cliente = New clsCliente()
                    End If


                    Dim ListaDetalle_Venta As New List(Of clsVentas_Detalle)
                    Dim Detalle_Venta_Dao As New daoVenta_Detalle()
                    ListaDetalle_Venta = Detalle_Venta_Dao.TraeDetallesVenta(Venta.Vta_Id)
                    Venta.Detalle = ListaDetalle_Venta
                    Detalle_Venta_Dao.Dispose()

                    ListaVentas.Add(Venta)
                    Venta.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return ListaVentas
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer Todas las Ventas por Cliente'
    Public Function TraeVentas(ByVal Cliente As clsCliente) As List(Of clsVentas)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaVentas As New List(Of clsVentas)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_VENTAS_X_CLIENTE"

            Cm.Parameters.Add("@cli_id", SqlDbType.Int)
            Cm.Parameters("@cli_id").Value = Cliente.Cli_Id

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Venta As New clsVentas()
                    Venta.Vta_Id = Dr.GetInt32(0)
                    Venta.Vta_Fecha = Dr.GetDateTime(1)
                    If Dr.IsDBNull(2) Then
                        Venta.Cli_Id = 0
                    Else
                        Venta.Cli_Id = Dr.GetInt32(2)
                    End If

                    Venta.Presu_Id = Dr.GetInt32(3)
                    If Dr.IsDBNull(3) Then
                        Venta.Presu_Id = 0
                    Else
                        Venta.Presu_Id = Dr.GetInt32(3)
                    End If

                    Venta.Emp_Id = Dr.GetInt32(4)
                    Venta.Vta_Importe = Dr.GetDecimal(5)
                    Venta.Cod_Suc = Dr.GetInt32(6)

                    If (Venta.Emp_Id > 0) Then
                        Dim Empleado_Dao As New daoEmpleado()
                        Venta.Empleado = Empleado_Dao.TraeEmpleado(Venta.Emp_Id)
                        Empleado_Dao.Dispose()
                    Else
                        Venta.Empleado = New clsEmpleado()
                    End If

                    If (Venta.Cli_Id > 0) Then
                        Dim Cliente_Dao As New daoCliente()
                        Venta.Cliente = Cliente_Dao.TraeCliente(Venta.Cli_Id)
                        Cliente_Dao.Dispose()
                    Else
                        Venta.Cliente = New clsCliente()
                    End If


                    Dim ListaDetalle_Venta As New List(Of clsVentas_Detalle)
                    Dim Detalle_Venta_Dao As New daoVenta_Detalle()
                    ListaDetalle_Venta = Detalle_Venta_Dao.TraeDetallesVenta(Venta.Vta_Id)
                    Venta.Detalle = ListaDetalle_Venta
                    Detalle_Venta_Dao.Dispose()

                    ListaVentas.Add(Venta)
                    Venta.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return ListaVentas
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function

    'Funcion para Traer Todas las Ventas por Empleado'
    Public Function TraeVentas(ByVal Empleado As clsEmpleado) As List(Of clsVentas)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaVentas As New List(Of clsVentas)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_VENTAS_X_EMPLEADO"

            Cm.Parameters.Add("@emp_id", SqlDbType.Int)
            Cm.Parameters("@emp_id").Value = Empleado.Emp_Id

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Venta As New clsVentas()
                    Venta.Vta_Id = Dr.GetInt32(0)
                    Venta.Vta_Fecha = Dr.GetDateTime(1)
                    If Dr.IsDBNull(2) Then
                        Venta.Cli_Id = 0
                    Else
                        Venta.Cli_Id = Dr.GetInt32(2)
                    End If

                    Venta.Presu_Id = Dr.GetInt32(3)
                    If Dr.IsDBNull(3) Then
                        Venta.Presu_Id = 0
                    Else
                        Venta.Presu_Id = Dr.GetInt32(3)
                    End If

                    Venta.Emp_Id = Dr.GetInt32(4)
                    Venta.Vta_Importe = Dr.GetDecimal(5)
                    Venta.Cod_Suc = Dr.GetInt32(6)

                    If (Venta.Emp_Id > 0) Then
                        Dim Empleado_Dao As New daoEmpleado()
                        Venta.Empleado = Empleado_Dao.TraeEmpleado(Venta.Emp_Id)
                        Empleado_Dao.Dispose()
                    Else
                        Venta.Empleado = New clsEmpleado()
                    End If

                    If (Venta.Cli_Id > 0) Then
                        Dim Cliente_Dao As New daoCliente()
                        Venta.Cliente = Cliente_Dao.TraeCliente(Venta.Cli_Id)
                        Cliente_Dao.Dispose()
                    Else
                        Venta.Cliente = New clsCliente()
                    End If


                    Dim ListaDetalle_Venta As New List(Of clsVentas_Detalle)
                    Dim Detalle_Venta_Dao As New daoVenta_Detalle()
                    ListaDetalle_Venta = Detalle_Venta_Dao.TraeDetallesVenta(Venta.Vta_Id)
                    Venta.Detalle = ListaDetalle_Venta
                    Detalle_Venta_Dao.Dispose()

                    ListaVentas.Add(Venta)
                    Venta.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return ListaVentas
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function


    '---------------------------------------------------------------------
    ' Constructor de la Clase
    '---------------------------------------------------------------------
    Public Sub New()

        MyBase.New()

    End Sub

    '---------------------------------------------------------------------
    ' Destructor de la Clase
    '---------------------------------------------------------------------
    Public Sub Dispose()

        MyBase.Finalize()

    End Sub


End Class
